import sqlite3
from typing import Optional, List, Dict, Any
from aiohttp import web
from pydantic import BaseModel, field_validator, Field  # 改用field_validator
from ..core.database.connection import db_manager


# 数据模型验证
class TranslateCreate(BaseModel):
    en: str
    cn: str
    hit_count: Optional[int] = Field(0, ge=0)


class TranslateDelete(BaseModel):
    en: str

class TranslateMentionQuery(BaseModel):
    query: str  # 查询关键词，不能为空
    page: int = Field(1, ge=1)
    page_size: int = Field(50, ge=1, le=100)  # 默认分页大小为50，范围1-100
    sort_field: str = 'hit_count'
    sort_order: str = 'DESC'

class TranslateUpdate(BaseModel):
    en: str
    cn: Optional[str] = None
    hit_count: Optional[int] = Field(None, ge=0)

    @field_validator('cn', 'hit_count')  # 改用field_validator
    def check_at_least_one_field(cls, v, values):  # 移除info参数
        # 确保至少提供一个可更新字段
        if 'cn' not in values and 'hit_count' not in values:
            raise ValueError('至少需要提供一个有效字段进行修改')
        return v


class TranslatePagedQuery(BaseModel):
    page: int = Field(1, ge=1)
    page_size: int = Field(10, ge=1, le=100)
    en: Optional[str] = None
    cn: Optional[str] = None
    hit_count_min: Optional[int] = Field(None, ge=0)
    hit_count_max: Optional[int] = Field(None, ge=0)
    sort_field: str = 'en'
    sort_order: str = 'ASC'


class TranslateTagsRequest(BaseModel):
    tags: List[str]

    @field_validator('tags')  # 改用field_validator
    def check_tags_non_empty(cls, v):
        if not v or len(v) == 0:
            raise ValueError('tags必须是非空数组')
        for tag in v:
            if not isinstance(tag, str):
                raise ValueError('每个标签必须是字符串')
        return v


class TranslateBatchItem(BaseModel):
    en: str
    cn: str
    hit_count: Optional[int] = Field(0, ge=0)


class TranslateBatchCreate(BaseModel):
    translations: List[TranslateBatchItem]

    @field_validator('translations')
    def check_translations_non_empty(cls, v):
        if not v or len(v) == 0:
            raise ValueError('translations必须是非空数组')
        return v

# 通用工具函数
def dict_from_row(row: sqlite3.Row) -> Dict[str, Any]:
    """将sqlite3.Row转换为字典"""
    if row is None:
        return None
    return {key: row[key] for key in row.keys()}


# 业务逻辑服务
class TranslateService:
    @staticmethod
    async def add_translate(en: str, cn: str, hit_count: int = 0) -> Dict[str, Any]:
        """添加翻译记录"""
        with db_manager.get_connection() as conn:
            try:
                cursor = conn.execute("""
                    INSERT INTO translate (en, cn, hit_count)
                    VALUES (?, ?, ?)
                """, [en, cn, hit_count])

                return {"result": cursor.rowcount}
            except sqlite3.IntegrityError as e:
                raise ValueError(f"数据库完整性错误: {str(e)}")
            except Exception as e:
                raise e

    @staticmethod
    async def delete_translate(en: str) -> Dict[str, Any]:
        """删除翻译记录"""
        with db_manager.get_connection() as conn:
            try:
                # 检查记录是否存在
                exists = conn.execute("""
                    SELECT 1 FROM translate WHERE en = ?
                """, [en]).fetchone()

                if not exists:
                    raise web.HTTPNotFound(reason='翻译记录不存在')

                # 执行删除
                cursor = conn.execute("""
                    DELETE FROM translate WHERE en = ?
                """, [en])

                return {"result": cursor.rowcount}
            except Exception as e:
                raise e

    @staticmethod
    async def update_translate(en: str, cn: Optional[str] = None, hit_count: Optional[int] = None) -> Dict[str, Any]:
        """更新翻译记录"""
        with db_manager.get_connection() as conn:
            try:
                # 检查记录是否存在
                exists = conn.execute("""
                    SELECT 1 FROM translate WHERE en = ?
                """, [en]).fetchone()

                if not exists:
                    raise web.HTTPNotFound(reason='翻译记录不存在')

                # 构建更新字段
                updates = ['updated_at = CURRENT_TIMESTAMP']
                params = []

                if cn is not None:
                    updates.append("cn = ?")
                    params.append(cn)

                if hit_count is not None:
                    updates.append("hit_count = ?")
                    params.append(hit_count)

                # 添加WHERE条件参数
                params.append(en)

                # 执行更新
                conn.execute(f"""
                    UPDATE translate SET {', '.join(updates)} WHERE en = ?
                """, params)

                # 获取更新后的完整数据
                updated = conn.execute("""
                    SELECT en, cn, hit_count, created_at, updated_at
                    FROM translate WHERE en = ?
                """, [en]).fetchone()

                return {"data": dict_from_row(updated)}
            except Exception as e:
                raise e

    @staticmethod
    async def get_translate_paged(
            page: int,
            page_size: int,
            en: Optional[str] = None,
            cn: Optional[str] = None,
            hit_count_min: Optional[int] = None,
            hit_count_max: Optional[int] = None,
            sort_field: str = 'hit_count',
            sort_order: str = 'DESC'
    ) -> Dict[str, Any]:
        """分页查询翻译记录"""
        # 验证排序字段和方向
        allowed_fields = ['en', 'cn', 'hit_count', 'created_at', 'updated_at']
        order_by = sort_field if sort_field in allowed_fields else 'hit_count'
        order_dir = sort_order.upper() if sort_order.upper() in ['ASC', 'DESC'] else 'ASC'

        # 构建查询条件
        where = []
        params = []

        if en:
            where.append("en LIKE ?")
            params.append(f'%{en}%')

        if cn:
            where.append("cn LIKE ?")
            params.append(f'%{cn}%')

        if hit_count_min is not None:
            where.append("hit_count >= ?")
            params.append(hit_count_min)

        if hit_count_max is not None:
            where.append("hit_count <= ?")
            params.append(hit_count_max)

        where_clause = f"WHERE {' AND '.join(where)}" if where else ""

        # 计算偏移量
        offset = (page - 1) * page_size

        with db_manager.get_connection() as conn:
            conn.row_factory = sqlite3.Row

            # 获取数据
            data_cursor = conn.execute(f"""
                SELECT en, cn, hit_count, created_at, updated_at
                FROM translate
                {where_clause}
                ORDER BY {order_by} {order_dir}
                LIMIT ? OFFSET ?
            """, params + [page_size, offset])

            data = [dict_from_row(row) for row in data_cursor.fetchall()]

            # 获取总数
            total_cursor = conn.execute(f"""
                SELECT COUNT(*) AS total FROM translate {where_clause}
            """, params)

            total = total_cursor.fetchone()['total']

            return {
                "data": data,
                "pagination": {
                    "total": total,
                    "page": page,
                    "page_size": page_size,
                    "total_pages": (total + page_size - 1) // page_size
                }
            }

    @staticmethod
    async def get_mention(
            query: str,
            page: int,
            page_size: int,
            sort_field: str = 'hit_count',
            sort_order: str = 'DESC'
    ) -> Dict[str, Any]:
        """查询提及的翻译记录"""
        # 验证排序字段和方向
        allowed_fields = ['en', 'cn', 'hit_count', 'created_at', 'updated_at']
        order_by = sort_field if sort_field in allowed_fields else 'hit_count'
        order_dir = sort_order.upper() if sort_order.upper() in ['ASC', 'DESC'] else 'ASC'

        # 构建查询条件
        where = []
        params = []

        if query:
            where.append("(en LIKE ? OR cn LIKE ?)")
            params.append(f'%{query}%')
            params.append(f'%{query}%')

        where_clause = f"WHERE {' AND '.join(where)}" if where else ""

        # 计算偏移量
        offset = (page - 1) * page_size

        with db_manager.get_connection() as conn:
            conn.row_factory = sqlite3.Row

            # 获取数据
            cursor = conn.execute(f"""
                SELECT en as value, cn as label
                FROM translate
                {where_clause}
                ORDER BY {order_by} {order_dir}
                LIMIT ? OFFSET ?
            """, params + [page_size, offset])

            data = [dict_from_row(row) for row in cursor.fetchall()]

            return {"data": data}
    @staticmethod
    async def translate_tags(tags: List[str]) -> Dict[str, Any]:
        """翻译标签列表"""
        # 去重处理并保留原始大小写映射
        original_case_map: Dict[str, str] = {}
        unique_lower_tags: List[str] = []

        for tag in tags:
            clean_tag = tag.strip()
            lower_tag = clean_tag.lower()

            if lower_tag not in original_case_map:
                original_case_map[lower_tag] = clean_tag
                unique_lower_tags.append(lower_tag)

        if not unique_lower_tags:
            return {"translations": {}}

        # 批量查询数据库
        placeholders = ', '.join(['?'] * len(unique_lower_tags))

        with db_manager.get_connection() as conn:
            conn.row_factory = sqlite3.Row
            cursor = conn.execute(f"""
                SELECT en, cn
                FROM translate
                WHERE en IN ({placeholders})
            """, unique_lower_tags)

            db_result = [dict_from_row(row) for row in cursor.fetchall()]

        # 构建翻译结果
        translations: Dict[str, str] = {}

        for row in db_result:
            original_tag = original_case_map.get(row['en'].lower())
            if original_tag:
                translations[original_tag] = row['cn']

        # 填充未翻译的标签（返回原始标签）
        for lower_tag in unique_lower_tags:
            original_tag = original_case_map[lower_tag]
            if original_tag not in translations:
                translations[original_tag] = original_tag

        return {"translations": translations}

    @staticmethod
    async def update_hit_count(tags: List[str]) -> Dict[str, Any]:
        """更新标签命中计数"""
        # 转换为小写并去重
        unique_lower_tags = list({tag.lower() for tag in tags})

        if not unique_lower_tags:
            return {"success": True}

        # 执行批量更新
        placeholders = ', '.join(['?'] * len(unique_lower_tags))

        with db_manager.get_connection() as conn:
            conn.execute(f"""
                UPDATE translate
                SET hit_count = hit_count + 1
                WHERE en IN ({placeholders})
            """, unique_lower_tags)

        return {"success": True}

    @staticmethod
    async def batch_add_translate(translations: List[TranslateBatchItem]) -> Dict[str, Any]:
        """批量添加翻译记录，存在则更新cn字段"""
        with db_manager.get_connection() as conn:
            try:
                # 构建批量插入的占位符 (?, ?, ?), (?, ?, ?), ...
                placeholders = ', '.join(['(?, ?, ?)'] * len(translations))

                # 提取参数值
                values = []
                for item in translations:
                    values.append(item.en)
                    values.append(item.cn)
                    values.append(item.hit_count or 0)  # 默认为0

                # 执行批量插入（冲突时更新cn）
                query = f"""
                    INSERT INTO translate (en, cn, hit_count)
                    VALUES {placeholders}
                    ON CONFLICT(en) DO UPDATE SET 
                        cn = excluded.cn
                """

                cursor = conn.execute(query, values)

                return {
                    "success": True,
                    "insertedCount": cursor.rowcount,
                    "totalReceived": len(translations)
                }
            except sqlite3.IntegrityError as e:
                raise ValueError(f"数据库完整性错误: {str(e)}")
            except Exception as e:
                raise e

# 路由处理函数
async def handle_add_translate(request: web.Request) -> web.Response:
    try:
        data = await request.json()
        translate_data = TranslateCreate(** data)

        result = await TranslateService.add_translate(
            en=translate_data.en,
            cn=translate_data.cn,
            hit_count=translate_data.hit_count
        )
        return web.json_response(result)
    except ValueError as e:
        return web.json_response({"error": str(e)}, status=400)
    except Exception as e:
        return web.json_response(
            {"error": str(e)},
            status=500
        )


async def handle_delete_translate(request: web.Request) -> web.Response:
    try:
        data = await request.json()
        delete_data = TranslateDelete(**data)

        result = await TranslateService.delete_translate(
            en=delete_data.en
        )
        return web.json_response(result)
    except web.HTTPNotFound as e:
        return web.json_response({"error": e.reason}, status=404)
    except ValueError as e:
        return web.json_response({"error": str(e)}, status=400)
    except Exception as e:
        return web.json_response(
            {"error": str(e)},
            status=500
        )


async def handle_update_translate(request: web.Request) -> web.Response:
    try:
        data = await request.json()
        update_data = TranslateUpdate(** data)

        result = await TranslateService.update_translate(
            en=update_data.en,
            cn=update_data.cn,
            hit_count=update_data.hit_count
        )
        return web.json_response(result)
    except web.HTTPNotFound as e:
        return web.json_response({"error": e.reason}, status=404)
    except ValueError as e:
        return web.json_response({"error": str(e)}, status=400)
    except Exception as e:
        return web.json_response(
            {"error": str(e)},
            status=500
        )


async def handle_get_translate_paged(request: web.Request) -> web.Response:
    try:
        # 解析查询参数
        params = TranslatePagedQuery(**request.query)

        result = await TranslateService.get_translate_paged(
            page=params.page,
            page_size=params.page_size,
            en=params.en,
            cn=params.cn,
            hit_count_min=params.hit_count_min,
            hit_count_max=params.hit_count_max,
            sort_field=params.sort_field,
            sort_order=params.sort_order
        )
        return web.json_response(result)
    except ValueError as e:
        return web.json_response({"error": str(e)}, status=400)
    except Exception as e:
        return web.json_response(
            {"error": str(e)},
            status=500
        )


async def handle_translate_tags(request: web.Request) -> web.Response:
    try:
        data = await request.json()
        tags_request = TranslateTagsRequest(** data)

        result = await TranslateService.translate_tags(
            tags=tags_request.tags
        )
        return web.json_response(result)
    except ValueError as e:
        return web.json_response({"error": str(e)}, status=400)
    except Exception as e:
        return web.json_response(
            {
                "error": "翻译服务暂时不可用",
                "details": str(e)
            },
            status=500
        )


async def handle_update_hit_count(request: web.Request) -> web.Response:
    try:
        data = await request.json()
        tags_request = TranslateTagsRequest(**data)

        result = await TranslateService.update_hit_count(
            tags=tags_request.tags
        )
        return web.json_response(result)
    except ValueError as e:
        return web.json_response({"error": str(e)}, status=400)
    except Exception as e:
        return web.json_response(
            {
                "error": "命中计数更新失败",
                "details": str(e)
            },
            status=500
        )

async def handle_get_mention(request: web.Request) -> web.Response:
    try:
        # 解析查询参数
        params = TranslateMentionQuery(**request.query)

        result = await TranslateService.get_mention(
            query=params.query,
            page=params.page,
            page_size=params.page_size,
            sort_field=params.sort_field,
            sort_order=params.sort_order
        )
        return web.json_response(result)
    except ValueError as e:
        return web.json_response({"error": str(e)}, status=400)
    except Exception as e:
        return web.json_response(
            {"error": "数据库错误", "details": str(e)},
            status=500
        )


async def handle_batch_add_translate(request: web.Request) -> web.Response:
    try:
        data = await request.json()
        batch_data = TranslateBatchCreate(**data)

        result = await TranslateService.batch_add_translate(
            translations=batch_data.translations
        )
        return web.json_response(result)
    except ValueError as e:
        return web.json_response({"error": str(e)}, status=400)
    except Exception as e:
        print(f"批量插入翻译记录错误: {str(e)}")
        return web.json_response(
            {
                "error": str(e) if isinstance(e, Error) else "数据库错误",
                "message": "批量插入翻译记录失败"
            },
            status=500
        )


async def handle_delete_translates_by_filter(request: web.Request) -> web.Response:
    """按筛选条件删除所有符合条件的翻译记录"""
    try:
        # 解析查询参数（与get_translate_paged相同）
        en = request.query.get('en')
        cn = request.query.get('cn')
        hit_count_min = float(request.query.get('hit_count_min')) if request.query.get('hit_count_min') else None
        hit_count_max = float(request.query.get('hit_count_max')) if request.query.get('hit_count_max') else None

        # 构建查询条件
        where: List[str] = []
        params: List[Any] = []

        if en:
            where.append("en LIKE ?")
            params.append(f'%{en}%')

        if cn:
            where.append("cn LIKE ?")
            params.append(f'%{cn}%')

        if hit_count_min is not None:
            where.append("hit_count >= ?")
            params.append(hit_count_min)

        if hit_count_max is not None:
            where.append("hit_count <= ?")
            params.append(hit_count_max)

        # 如果没有任何筛选条件，拒绝删除所有记录
        if not where:
            return web.json_response({
                "error": "删除操作必须提供至少一个筛选条件，以防止误删除所有记录"
            }, status=400)

        where_clause = f"WHERE {' AND '.join(where)}" if where else ""

        # 执行删除操作
        with db_manager.get_connection() as conn:
            # 先查询符合条件的记录数量
            count_cursor = conn.execute(f"""
                SELECT COUNT(*) AS total FROM translate {where_clause}
            """, params)
            total = count_cursor.fetchone()[0]

            if total == 0:
                return web.json_response({
                    "success": True,
                    "message": "未找到符合条件的记录",
                    "deleted_count": 0
                })

            # 执行删除
            delete_cursor = conn.execute(f"""
                DELETE FROM translate {where_clause}
            """, params)
            conn.commit()

            return web.json_response({
                "success": True,
                "message": f"成功删除 {delete_cursor.rowcount} 条记录",
                "deleted_count": delete_cursor.rowcount
            })

    except ValueError as e:
        return web.json_response({"error": str(e)}, status=400)
    except Exception as e:
        return web.json_response({"error": str(e)}, status=500)

# 路由配置
routes = [
    # 添加翻译记录
    web.post("/sql/add_translate", handle_add_translate),
    # 删除翻译记录
    web.post("/sql/delete_translate", handle_delete_translate),
    # 更新翻译记录
    web.put("/sql/update_translate", handle_update_translate),
    # 分页查询翻译记录
    web.get("/sql/get_translate_paged", handle_get_translate_paged),
    # 翻译标签
    web.post("/translate/tags", handle_translate_tags),
    # 更新命中计数
    web.post("/translate/update-hit-count", handle_update_hit_count),
    web.get("/sql/get_mention", handle_get_mention),
    web.post("/sql/batch_add_translate", handle_batch_add_translate),
    web.delete("/sql/delete_translates_by_filter", handle_delete_translates_by_filter)
]
